package com.xuyifeng.callfilter;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.preference.PreferenceManager;
import android.util.Log;

public class FilterDBAdapter {
	private static final String DATABASE_NAME  = "blocked.db";
	private static final String TABLE_BLACKLIST = "blacklist";
	private static final String TABLE_WHITELIST = "whitelist";
	private static final String TABLE_LOG = "log";
	
	private static final int DATABASE_VERSION = 11;
	
	public static final String KEY_ID = "_id";
	public static final String KEY_CREATION_DATE = "creation_date";
	public static final String KEY_PHONE = "phone";
	public static final String KEY_MEMO = "memo";
	
	private SQLiteDatabase		m_db;
	private FilterDBOpenHelper m_dbHelper;
	Context m_context;
	
	private static final String LOG_TAG = "FilterDBAdapter";
	
	private static class FilterDBOpenHelper extends SQLiteOpenHelper {
		public FilterDBOpenHelper(Context context, String name, CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		// SQL Statement to create a new database.
		private static final String BLACKLIST_CREATE = "create table " +
			TABLE_BLACKLIST + " (" + 
			KEY_ID + " varchar(32) primary key, " +
			KEY_CREATION_DATE + " long," +
			KEY_MEMO + " varchar(64)" +
			");";
		private static final String BLACKLIST_INDEX_DATE = "CREATE INDEX blacklist_date_idx ON " +
			TABLE_BLACKLIST + "(" + KEY_CREATION_DATE + ")"; 


		private static final String WHITELIST_CREATE = "create table " +
			TABLE_WHITELIST + " (" + 
			KEY_ID + " varchar(32) primary key, " +
			KEY_CREATION_DATE + " long," +
			KEY_MEMO + " varchar(64)" +
			");";
		private static final String WHITELIST_INDEX_DATE = "CREATE INDEX whitelist_date_idx ON " +
			TABLE_WHITELIST + "(" + KEY_CREATION_DATE + ")"; 

	
		private static final String LOG_CREATE = "create table " +
			TABLE_LOG + " (" + 
			KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
			KEY_PHONE + " varchar(32)," +
			KEY_CREATION_DATE + " long," +
			KEY_MEMO + " varchar(64)" +
			");";
	
		private static final String LOG_INDEX_DATE = "CREATE INDEX log_date_idx ON " +
			TABLE_LOG + "(" + KEY_CREATION_DATE + ")"; 
			
		@Override
		public void onCreate(SQLiteDatabase _db) {
			_db.execSQL(BLACKLIST_CREATE);
			_db.execSQL(BLACKLIST_INDEX_DATE);
			_db.execSQL(WHITELIST_CREATE);
			_db.execSQL(WHITELIST_INDEX_DATE);
			_db.execSQL(LOG_CREATE);
			_db.execSQL(LOG_INDEX_DATE);
		}
		
		@Override
		public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {
			Log.w(LOG_TAG, "Upgrading from version " +
					_oldVersion + " to " +
					_newVersion + ", which will destroy all old data");
			// Drop the old table.
			_db.execSQL("DROP TABLE IF EXISTS " + TABLE_BLACKLIST);
			_db.execSQL("DROP TABLE IF EXISTS " + TABLE_WHITELIST);
			_db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOG);
			// Create a new one.
			onCreate(_db);
		}
	}
	
	public FilterDBAdapter(Context aContext) {
		m_context = aContext;
		m_dbHelper = new FilterDBOpenHelper(aContext, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	public void close() {
		m_db.close();
	}
	
	public void open() throws SQLiteException {
		try {
			m_db = m_dbHelper.getWritableDatabase();
		} catch (SQLiteException ex) {
			m_db = m_dbHelper.getReadableDatabase();
		}
	}
	
	public long insertBlackList(String phone, String memo) {
		ContentValues newValues = new ContentValues();
		
		newValues.put(KEY_ID, phone);
		newValues.put(KEY_CREATION_DATE, System.currentTimeMillis());
		newValues.put(KEY_MEMO, memo);
		return m_db.insert(TABLE_BLACKLIST, null, newValues);
	}
	
	public void removeBlackList(String phone) {
		m_db.delete(TABLE_BLACKLIST, KEY_ID + "=?", new String[] { phone} );
	}
	
	public long insertWhiteList(String phone, String memo) {
		ContentValues newValues = new ContentValues();
		
		newValues.put(KEY_ID, phone);
		newValues.put(KEY_CREATION_DATE, System.currentTimeMillis());
		newValues.put(KEY_MEMO, memo);
		return m_db.insert(TABLE_WHITELIST, null, newValues);
	}
	
	public void removeWhiteList(String phone) {
		m_db.delete(TABLE_WHITELIST, KEY_ID + "=?", new String[] {phone});
	}
	
	public Cursor getAllBlackListCursor() {
		return m_db.query(TABLE_BLACKLIST,
				new String[] { KEY_ID, KEY_CREATION_DATE, KEY_MEMO},
				null, null, null, null, KEY_CREATION_DATE + " DESC");
	}
	
	public Cursor getAllWhiteListCursor() {
		return m_db.query(TABLE_WHITELIST,
				new String[] { KEY_ID, KEY_CREATION_DATE, KEY_MEMO },
				null, null, null, null, KEY_CREATION_DATE + " DESC");
	}
	
	public Cursor getAllLogCursor() {
		return m_db.query(TABLE_LOG,
				new String[] { KEY_ID, KEY_PHONE, KEY_CREATION_DATE, KEY_MEMO },
				null, null, null, null, KEY_CREATION_DATE + " DESC");
	}
	
	public void updateBlackListMemo(String phone, String memo) {
		ContentValues newValues = new ContentValues();
		String where = KEY_ID + "=?";
		String[] whereArgs = {phone};
		newValues.put(KEY_MEMO, memo);
		m_db.update(TABLE_BLACKLIST, newValues, where, whereArgs);
	}
	
	public void updateWhiteListMemo(String phone, String memo) {
		ContentValues newValues = new ContentValues();
		String where = KEY_ID + "=?";
		String[] whereArgs = {phone};
		newValues.put(KEY_MEMO, memo);
		m_db.update(TABLE_WHITELIST, newValues, where, whereArgs);
	}
	
	public boolean isInBlackList(String phone) {
		Cursor c = m_db.query(TABLE_BLACKLIST, new String[] { KEY_ID },
				KEY_ID +"=?", new String[]{phone}, null, null, null, null);
		Log.d("FilterDBAdapter", "isInBlackList, count: "+c.getCount());
		boolean yes = c.getCount() > 0; 
		c.close();
		return (yes);
	}
	
	public boolean isInWhiteList(String phone) {
		Cursor c = m_db.query(TABLE_WHITELIST, new String[] { KEY_ID },
				KEY_ID +"=?", new String[] {phone}, null, null, null, null);
		Log.d("FilterDBAdapter", "isInWhiteList, count: "+c.getCount());
		boolean yes = c.getCount() > 0; 
		c.close();
		return (yes);
	}
	
	public long enqueueLog(String phone, String memo) {
		SharedPreferences prefs = PreferenceManager.getDefaultSharedPreferences(m_context);
		int maxLogs = prefs.getInt(Settings.KEY_MAX_LOGS, 30); /* XXX */
		long entries = DatabaseUtils.queryNumEntries(m_db, TABLE_LOG);
		if (entries >= maxLogs) {
			String fields[] = { KEY_ID };
			Cursor c = m_db.query(TABLE_LOG, fields,
					null, null, null, null, KEY_CREATION_DATE + " ASC", "1");
			if (c.moveToFirst()) {
				String id = c.getString(0);
				m_db.delete(TABLE_LOG, KEY_ID + "=?", new String[] {id});
			}
			c.close();
		}

		ContentValues newValues = new ContentValues();
		newValues.put(KEY_PHONE, phone);
		newValues.put(KEY_MEMO, memo);
		newValues.put(KEY_CREATION_DATE, System.currentTimeMillis());
		return m_db.insert(TABLE_LOG, null, newValues);
	}
	
	public void clearAllLogs() {
		m_db.delete(TABLE_LOG, null, null);
	}
}